﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTGB02PSData : BaseFunction
    {
        
        public JLTGB02PSData()
            : base()
        {

        }
        public JLTGB02PSData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetGB02PS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "GB02PS";
            string ART = dwABPOS["ART"].ToString().Trim();
            string LB = dwABPOS["LB"].ToString().Trim();
            string SPPART = dwABPOS["SPPART"].ToString().Trim();
            //string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            //string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT a.gllb,TRIM(B.GPGART) AS ART,TRIM(C.GSHANDLNR) AS DES,TRIM(D.LSNAME2) AS SUPP, "
                    + " B.GPSPPART,SUM(A.GLBSTNET*0.001) AS KG,SUM(E.ALLO) AS ALLO,SUM(A.GLBSTNET*0.001)-nvl(SUM(E.ALLO),0) AS FREE,MAX(B.GPEINDAT) AS GPEINDAT "
                    + " FROM GLLG A,GPDAT B,GSDAT C,LIEFDAT D, "
                    + " (SELECT ZUMANDANT,ZUPARTIE,ZUPARTIEKZ,ZULBEHI,SUM((E.ZUEINTKG-E.ZUENTNKG)*0.001) AS ALLO "
                    + "  FROM ZUTEI E WHERE E.ZUFLAG = 'S' AND E.ZUERLKZ = ' ' "
                    + "  GROUP BY ZUMANDANT,ZUPARTIE,ZUPARTIEKZ,ZULBEHI) E "
                    + " WHERE A.GLMANDANT = '00' AND A.GLLO = '01' "
                    + " AND A.GLMANDANT = B.GPMANDANT AND A.GLPARTIE = B.GPPARTIE "
                    + " AND A.GLPARTIEKZ = B.GPPARTIEKZ AND B.GPMANDANT = C.GSMANDANT(+) "
                    + " AND B.GPGART = C.GSART(+) AND B.GPGVAR = C.GSVAR(+) "
                    + " AND B.GPMANDANT = D.LSMANDANT(+) AND B.GPLIEFNR = D.LSLIEFNR(+) "
                    + " AND A.GLMANDANT = E.ZUMANDANT(+) AND A.GLPARTIE = E.ZUPARTIE(+) "
                    + " AND A.GLPARTIEKZ = E.ZUPARTIEKZ(+) AND A.GLLBEHI = E.ZULBEHI(+) "
                    + " AND B.GPGART LIKE '%" + ART + "%' "
                    + " AND (TRIM(B.GPSPPART) = upper('"+SPPART+"') OR '"+SPPART+"' = 'AAAAAAAAAA' ) "
                    + " AND A.GLLB = '"+LB+"' "
                    + " GROUP BY a.gllb,B.GPGART,C.GSHANDLNR,D.LSNAME2,B.GPSPPART "                    
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DES"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SUPP"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SUPP"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["GPSPPART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["GPSPPART"] = value;;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTGB02PSData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetGB02PS(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}